package ru.spbu.math.android.cbrf.dataprovider;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import ru.spbu.math.android.cbrf.model.Rate;
import ru.spbu.math.android.cbrf.model.StockItem;

import java.util.Date;
import java.util.LinkedList;
import java.util.StringTokenizer;

/**
 * This class is realization of connection with application database. It has
 * methods that return Rate objects with concrete values of name and date. TODO:
 * add more query methods with different parameters sets. Also RateDatabase has
 * methods providing addition of Rate objects into database.
 * <p/>
 * NB! Database doesn't take into consideration hours, minutes and so on. Only
 * first three Date fields are meaningful.
 * 
 * @author Olga Nikonova
 */

public class RateDatabase {

	private SQLiteDatabase db;
	private static final String TABLE_NAME = "rates";
	private static final String SOURCE = "source";
	private static final String TARGET = "target";
	private static final String VALUE = "value";
	private static final String DATE = "date";
	private static final String[] columns = { SOURCE, TARGET, VALUE, DATE };

	private static final String TAG = "RateDatabase";

	// /**
	// * @return latest Date in database
	// */
	// public Date getLatestUpdateTime() {
	//
	// String[] selectColumns = { DATE };
	// Cursor cursor = db.query(TABLE_NAME, selectColumns, null, null, null,
	// null, DATE + " DESC");
	//
	// if (!cursor.moveToFirst()) {
	// cursor.close();
	// return null;
	// } else {
	// Date res = timestringToDate(cursor.getString(cursor
	// .getColumnIndex(DATE)));
	// cursor.close();
	// return res;
	// }
	// }
	//
	// /**
	// * @param source
	// * @param target
	// * @return latest Rate.date value for Rates with fit source and target
	// */
	// public Date getLatestUpdateTime(StockItem source, StockItem target) {
	//
	// String[] selectColumns = { DATE };
	// String selection = "( " + SOURCE + " = \"" + source + "\" ) AND ( "
	// + TARGET + " = \"" + target + "\" )";
	// Cursor cursor = db.query(TABLE_NAME, selectColumns, selection, null,
	// null, null, DATE + " DESC");
	//
	// if (!cursor.moveToFirst()) {
	// cursor.close();
	// return null;
	// } else {
	// Date res = timestringToDate(cursor.getString(cursor
	// .getColumnIndex(DATE)));
	// cursor.close();
	// return res;
	// }
	// }

	// /**
	// * @param source
	// * @param target
	// * @param date
	// * @return Rate with concrete values of date and name
	// */
	// private Rate getRate(StockItem source, StockItem target, Date date) {
	//
	// String[] selectColumns = { SOURCE, TARGET, VALUE,
	// "date(" + DATE + ") as day", "time(" + DATE + ") as time" };
	//
	// String selection = "( " + SOURCE + " = \"" + source.getCode()
	// + "\" ) AND ( " + TARGET + " = \"" + target.getCode()
	// + "\" ) AND ( day = \"" + dateToTimestring(date) + "\")";
	//
	// Cursor cursor = db.query(true, TABLE_NAME, selectColumns, selection,
	// null, null, null, null, "time DESC");
	//
	// if (!cursor.moveToFirst()) {
	// cursor.close();
	// return null;
	// } else {
	// double value = Double.parseDouble(cursor.getString(cursor
	// .getColumnIndex(VALUE)));
	// cursor.close();
	//
	// return new Rate(source.getCode(), target.getCode(), value, date);
	// }
	//
	// }

	/**
	 * @param rate
	 *            to be added into database
	 */
	public void addRate(Rate rate) {

		// if (!(getRate(rate.getSource(), rate.getTarget(), rate.getDate())
		// .getValue() == rate.getValue())) {

		if (rate != null) {
			ContentValues values = new ContentValues();
			values.put(SOURCE, rate.getSource().getCode());
			values.put(TARGET, rate.getTarget().getCode());
			values.put(VALUE, rate.getValue());
			values.put(DATE, dateToTimestring(rate.getDate()));
			// TODO what is nullColoumnHack?
			db.insertOrThrow(TABLE_NAME, null, values);
		}
		// }
	}

	public void addRates(Rate[] rates) {
		if (rates != null) {
			for (int i = 0; i < rates.length; i++) {
				addRate(rates[i]);
			}
		}
	}

	public Rate getLatestRate(StockItem source, StockItem target) {

		String[] selectColumns = { SOURCE, TARGET, VALUE, DATE,
				"date(" + DATE + ") as day", "time(" + DATE + ") as time" };

		String selection = "( " + SOURCE + " = \"" + source.getCode()
				+ "\" ) AND ( " + TARGET + " = \"" + target.getCode() + "\" )";

		Cursor cursor = db.query(TABLE_NAME, selectColumns, selection, null,
				null, null, "day DESC, time DESC");

		if (!cursor.moveToFirst()) {
			Log.d(TAG, "Getting Latest Rate Failed");
			cursor.close();
			return null;
		} else {
			Log.d(TAG, "Getting Latest Rate");
			double value = Double.parseDouble(cursor.getString(cursor
					.getColumnIndex(VALUE)));

			Date date = timestringToDate(cursor.getString(cursor
					.getColumnIndex(DATE)));
			cursor.close();

			return new Rate(source.getCode(), target.getCode(), value, date);
		}
	}

	/**
	 * constructor
	 * 
	 * @param context
	 * @throws SQLiteException
	 */
	public RateDatabase(Context context) throws SQLiteException {

		RateDatabaseOpenHelper open = new RateDatabaseOpenHelper(context);
		db = open.getWritableDatabase();
	}

	private static class RateDatabaseOpenHelper extends SQLiteOpenHelper {

		private static final String DATABASE_NAME = "rates.db";

		private static final String DATABASE_CREATE = "CREATE TABLE "
				+ TABLE_NAME + " (" + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ columns[0] + " TEXT," + columns[1] + " TEXT," + columns[2]
				+ " TEXT," + columns[3] + " TEXT);";

		private static final int DATABASE_VERSION = 1;

		RateDatabaseOpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME);
			onCreate(db);
		}
	}

	// make time string "YYYY-MM-DD HH:MM" from Date

	private String dateToTimestring(Date date) {

		String timestring = "";

		int year = date.getYear() + 1900;
		timestring += year;

		int month = date.getMonth() + 1;
		if (month < 10) {
			timestring += "-0" + month;
		} else {
			timestring += "-" + month;
		}

		if (date.getDate() < 10) {
			timestring += "-0" + date.getDate();
		} else {
			timestring += "-" + date.getDate();
		}

		if (date.getHours() < 10) {
			timestring += " 0" + date.getHours();
		} else {
			timestring += " " + date.getHours();
		}

		if (date.getMinutes() < 10) {
			timestring += ":0" + date.getMinutes();
		} else {
			timestring += ":" + date.getMinutes();
		}

		return timestring;
	}

	// build Date from time string "YYYY-MM-DD HH:MM"

	private Date timestringToDate(String timestring) {
		Date date = new Date();

		StringTokenizer st = new StringTokenizer(timestring, "- :");
		date.setYear(Integer.parseInt(st.nextToken()) - 1900);
		date.setMonth(Integer.parseInt(st.nextToken()) - 1);
		date.setDate(Integer.parseInt(st.nextToken()));
		date.setHours(Integer.parseInt(st.nextToken()));
		date.setMinutes(Integer.parseInt(st.nextToken()));

		return date;
	}

	public Rate[] getDynamicRates(StockItem source, StockItem target,
			Date since, Date until) {

		String[] selectColumns = { SOURCE, TARGET, VALUE, DATE,
				"date(" + DATE + ") as day", "time(" + DATE + ") as time" };

		String selection = "( " + SOURCE + " = \"" + source.getCode()
				+ "\" ) AND ( " + TARGET + " = \"" + target.getCode()
				+ "\" ) AND ( day >= TIME(" + dateToTimestring(since)
				+ ") ) AND ( day <= TIME(" + dateToTimestring(until) + ") )";

		Cursor cursor = db.query(TABLE_NAME, selectColumns, selection, null,
				null, null, "day DESC, time DESC");

		double value;
		Date date;
		LinkedList<Rate> rates = new LinkedList<Rate>();

		if (cursor.moveToFirst()) {
			Log.d(TAG, "Get Dynamic Rates");
			value = Double.parseDouble(cursor.getString(cursor
					.getColumnIndex(VALUE)));

			date = timestringToDate(cursor.getString(cursor
					.getColumnIndex(DATE)));

			rates.addLast(new Rate(source.getCode(), target.getCode(), value,
					date));
		} else {
			Log.d(TAG, "Getting Dynamic Rates Failed");
			cursor.close();
			return null;
		}

		cursor.close();
		Rate[] res = new Rate[rates.size()];
		rates.toArray(res);
		return res;
	}

	/**
	 * @param source
	 * @param target
	 * @return latest Rate with value different from current Rate
	 */
	public Rate getPreviousRate(StockItem source, StockItem target) {

		String[] selectColumns = { SOURCE, TARGET, VALUE, DATE,
				"date(" + DATE + ") as day", "time(" + DATE + ") as time" };

		String selection = "( " + SOURCE + " = \"" + source.getCode()
				+ "\" ) AND ( " + TARGET + " = \"" + target.getCode() + "\" )";

		Cursor cursor = db.query(TABLE_NAME, selectColumns, selection, null,
				null, null, "day DESC, time DESC");

		if (cursor.moveToFirst()) {
			double currentValue = Double.parseDouble(cursor.getString(cursor
					.getColumnIndex(VALUE)));
			
			double value;
			while (cursor.moveToNext()) {
				value = Double.parseDouble(cursor.getString(cursor
						.getColumnIndex(VALUE)));
				if (!(value == currentValue)) {
					Date date = timestringToDate(cursor.getString(cursor
							.getColumnIndex(DATE)));
					cursor.close();
					return new Rate(source.getCode(), target.getCode(), value,
							date);
				}
			}
		}

		cursor.close();
		return null;
	}
}
